package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import simorg.domain.Assignment;
import simorg.util.DatabaseConnection;

public class AssignmentTable {

	
	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		    	query = "DROP TABLE IF EXISTS ASSIGNMENT";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS ASSIGNMENT" +
		    	" (ID INT AUTO_INCREMENT PRIMARY KEY, TITLE VARCHAR(50), ASSIGNED_DATE DATE, DUE_DATE DATE, NUM_COMPOUNDS INT, " + 
		    	"COURSE_ID INT)";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		    
		    Assignment a = new Assignment() ;
			a.setTitle("Test Assignment A");
			a.setDueDate(new Date());
			a.setAssignedDate(new Date());
			a.setNumCompoundsToComplete(10);
			a.setCourseId(3);
			save(dc, a);
			
			a = new Assignment() ;
			a.setTitle("Test Assignment B");
			a.setDueDate(new Date());
			a.setAssignedDate(new Date());
			a.setNumCompoundsToComplete(30);
			a.setCourseId(3);
			save(dc, a);
		
		   
		    System.out.println("Bootstrapped Assignments");
		    Collection<Assignment> assignments = findAll(dc);
		    for ( Assignment ass : assignments ) {
		    	System.out.println("ID:  " + ass.getId() + " - " + ass.getTitle());
		    }
		}
		finally {
			dc.getConnection().close();
		}
	}
	
	
	public static void save(DatabaseConnection dc, Assignment assignment) {
		try {
			String query = null;
			if ( assignment.getId() < 0 ) {
				query = "INSERT INTO ASSIGNMENT (TITLE, ASSIGNED_DATE, DUE_DATE, NUM_COMPOUNDS, COURSE_ID) " +
								"VALUES ( ?, ?, ?, ?, ?)";
				PreparedStatement insert = dc.getConnection().prepareStatement(query);
				fillPreparedStatement(assignment, insert);
				insert.executeUpdate();
				ResultSet rs = insert.getGeneratedKeys();
				while (rs.next()){
					assignment.setId(rs.getInt(1));
				}
			}
			else {
				// update existing course
				query = "UPDATE ASSIGNMENT SET TITLE=?, ASSIGNED_DATE = ?, DUE_DATE = ?, NUM_COMPOUNDS = ?, COURSE_ID = ? " +
						"WHERE ID=?";
				PreparedStatement update = dc.getConnection().prepareStatement(query);
				fillPreparedStatement(assignment, update);
				update.setInt(6, assignment.getId());
				update.executeUpdate();
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Assignment findById(DatabaseConnection dc, int id) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		ArrayList<Assignment> result = find(dc, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}

	public static Collection<Assignment>  findByCourseId(DatabaseConnection dc, int courseId) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("COURSE_ID", courseId));
		return find(dc, constraints);
	}

	public static Collection<Assignment> findAll(DatabaseConnection dc) {
		QueryConstraints constraints = new QueryConstraints();
		return find(dc, constraints);
	}
	
	
	public static void deleteById(DatabaseConnection db, int id)  {
		CompoundAssignmentJoinTable.setCompoundsForAssignment(db, id, null);
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		try {
			String query = "DELETE FROM ASSIGNMENT " + constraints.getParameterString();
			PreparedStatement stmt = db.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			stmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	
	
	public static ArrayList<Assignment> find(DatabaseConnection dc, QueryConstraints constraints) {
		ArrayList<Assignment> retval = new ArrayList<Assignment>();
		try {
			String query = "SELECT * FROM ASSIGNMENT " + constraints.getParameterString();
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				Assignment assignemnt = new Assignment();
				fillFromResultSet(assignemnt, rs);
		        retval.add(assignemnt);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}
	
	public static void fillFromResultSet(Assignment a,ResultSet rs) throws Exception {
		a.setId(rs.getInt("ID"));
		a.setTitle(rs.getString("TITLE"));
		a.setDueDate(new Date(rs.getDate("DUE_DATE").getTime()));
		a.setAssignedDate(new Date(rs.getDate("ASSIGNED_DATE").getTime()));
		a.setNumCompoundsToComplete(rs.getInt("NUM_COMPOUNDS"));
		a.setCourseId(rs.getInt("COURSE_ID"));
	}

	public static void fillPreparedStatement(Assignment a, PreparedStatement stmt) throws Exception {
		stmt.setString(1, a.getTitle());
		stmt.setDate(2, new java.sql.Date(a.getAssignedDate().getTime()));
		stmt.setDate(3,new java.sql.Date(a.getDueDate().getTime()));
		stmt.setInt(4, a.getNumCompoundsToComplete());
		stmt.setInt(5, a.getCourseId());
	}

}
